package Classes;



import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.Vector;

//this is class for oracle data base operation
public class DB_utils 
{
	private Connection dbConn;
	private Statement stmt;
	private String strSQL;
	private ResultSet rs;

	
	
	public void setDbConnection()
	{
		getDbConn();
		getStmt();
	}
	
	public void closeDbConnection()
	{
		try
        {
			getDbConn().close();
		} 
		catch (SQLException e) 
		{
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
	}
	
	public Vector<String> getColHeader() 
	{
		
		Vector<String> hdr = new Vector<String>();
		getRs(getStrSQL());
		
		try 
		{
			ResultSetMetaData rsmd = rs.getMetaData();
			int numColm = rsmd.getColumnCount();
			
		    for(int i = 1;i< numColm; i++)
		    {
			   hdr.add(rsmd.getColumnLabel(i));
		    }	
		}
		catch (SQLException e)
		{
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return hdr;
	}
	//this function get all fields from one column
	public Vector<String> getFieldSet(String fieldName) 
	{
		
		Vector<String> fld = new Vector<String>();
		getRs(getStrSQL());
		try
		{
		   while (rs.next())
		   {
			  fld.add(rs.getString(fieldName));
		   }
		 }
		  catch (Exception e) 
		  {
			// TODO: handle exception
		  }
		return fld;
	}
	
	
	//update record based on input query
	public void updateRecord(String query)
	{
		try 
		{
			int numRows = stmt.executeUpdate(query);
			if (numRows == 0)
			{
				System.out.println("update failed");
			}
			else
			{
				System.out.println("updated " + numRows + " row(s)");
			}				
		} 
		catch (SQLException e)
		{
			// TODO Auto-generated catch block
			e.printStackTrace();
		};
		
	}
	
	//get record base on query  
	public Vector<String> getRecord(String query)
	{
		Vector<String> record = new Vector<String>();
		 
	    try 
	    {
	    	getRs(query) ;
	    	ResultSetMetaData rsmd = rs.getMetaData();
			int numColm = rsmd.getColumnCount();						
			if (rs.next())
			{
			  for(int i =1;i< numColm+1;i++)
			   {				 
				  System.out.println(rs.getString(i));
                  record.add(rs.getString(i));
				  
			   }
			}
			else
			{
				System.out.println("no row returned");
			}
			
		} 
		catch (SQLException e) 
		{
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return record;
	}
	
	
	
	public Connection getDbConn() 
	{
		
			try 
			{
				Class.forName("oracle.jdbc.driver.OracleDriver");
				dbConn = DriverManager.getConnection("jdbc:oracle:thin:@localhost:1521:orant11g","ictoosd","ictoosd");
			} 
			catch (ClassNotFoundException e) 
			{
				// TODO Auto-generated catch block
				e.printStackTrace();
			} 
			catch (SQLException e) 
			{
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
		
		return dbConn;
	}
	
	public Statement getStmt() 
	{
		if(stmt == null)
		{
			try 
			{
				stmt =getDbConn().createStatement();
			} 
			catch (SQLException e) 
			{
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
		}
		return stmt;
	}

	public void setStrSQL(String strSQL) 
	{
		this.strSQL = strSQL;
	}

	public String getStrSQL() 
	{
		return strSQL;
	}

	

	public ResultSet getRs(String quiry) 
	{
		
			try
			{
				rs = getStmt().executeQuery(quiry);
			} 
			catch (SQLException e) 
			{
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
	
		return rs;
	}

}
